﻿SELECT     REGION, MARKET,  acct_mgr, acct_mgr AS x, 2 AS LVL, 2 AS ORD
FROM         (SELECT     TOP (100) PERCENT REGION, MARKET, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', 
                                              ACCT_MGR) - 2) ELSE acct_mgr END AS acct_mgr
                       FROM          SCORE_ACC_PROPERTIES.PROPERTIES
                       GROUP BY REGION, MARKET, CASE WHEN CHARINDEX('(', ACCT_MGR) > 0 THEN SUBSTRING(ACCT_MGR, 1, CHARINDEX('(', ACCT_MGR) - 2) 
                                              ELSE acct_mgr END
                       ORDER BY REGION, MARKET, ACCT_MGR) AS derivedtbl_
GROUP BY REGION, MARKET,acct_mgr
UNION
SELECT DISTINCT REGION, ' ' + REGION AS Expr1, REGION AS Expr2, REGION AS Expr3, 0 AS Expr4, 0 AS Expr5
FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1
UNION
SELECT DISTINCT REGION,   MARKET as Expr1, MARKET, MARKET AS Expr2, 1 AS Expr3, 1 AS Expr4
FROM         SCORE_ACC_PROPERTIES.PROPERTIES AS PROPERTIES_1
ORDER BY 1,2, 6, 4, 5